import pymysql


# # 数据库连接配置、库表名、创建sql插入sql配置
# mysqlconf = {
#     "links": {
#         "host": "192.168.2.117",
#         "port": 3307,
#         "user": "localuser",
#         "passwd": "4030aoii103",
#         "charset": "utf8"
#     },
#     "dbs": {
#         "work_object": {
#             "obj_0315_zhihu": {
#                 "creatsql": "(  `id` INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, `hashid` VARCHAR(32) NOT NULL UNIQUE,`name` VARCHAR(32) NOT NULL, `sex` VARCHAR(6) ,`business` varchar(255) ,`educations` varchar(255),`locations` varchar(255),`star` int(12),`fans` int(12),`praise` int(12),`answer` int(12))",
#                 "insertsql": "INSERT INTO `work_object`.`obj_0315_zhihu` VALUES ('0','${id}','${name}','${sex}', '${business}','${educations}','${locations}','${star}','${fans}','${praise}','${answer}')",

#             }
#         }
#     }
# }


class Con(object):
    """Mysql类"""

    def __init__(self, cfg, loger=logging):
        self.cfg = cfg
        self.loger = logging.getLogger(type(self).__name__)
        self.con = pymysql.Connect(**self.cfg["links"])

    def create(self):
        self.con.ping(True)
        """自动库表创建"""
        maxid = 0
        sqldb = f"create database {self.cfg['target']['db']} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"
        sqltb = f"create table  {self.cfg['target']['tb']} {self.cfg['dbs'][self.cfg['target']['db']][self.cfg['target']['tb']]['creatsql']} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"
        try:
            try:
                self.con.select_db(self.cfg['target']['db'])
            except Exception as e:
                with self.con.cursor() as cur:
                    cur.execute(sqldb)
                    self.loger.info(
                        f"Database {self.cfg['target']['db']} create success")
            try:
                with self.con.cursor() as cur:
                    cur.execute(sqltb)
                    self.loger.info(
                        f"Table {self.cfg['target']['tb']} create success")
            except Exception as e:
                self.loger.error(e)
        except Exception as e:
            self.loger.error(e)
        finally:
            return self

    def newconf(self, db, tb):
        """连接到库"""
        self.con.ping(True)
        self.cfg["target"] = {"db": db, "tb": tb}
        try:
            self.con.select_db(db)
        except Exception as e:
            self.create()
        return self

    def common_insert_sql(self, sqltype, data, debug=False):
        """输出插入，失败则回滚"""
        self.con.ping(True)
        if data:
            try:
                with self.con.cursor() as cur:
                    sql = tmp(self.cfg["dbs"][self.cfg["target"]["db"]][
                        self.cfg["target"]["tb"]][sqltype], data)
                    cur.execute(sql)
                    self.con.commit()
                    return True
            except Exception as e:
                self.loger.error(e)
                self.con.rollback()
